
import requests
from bs4 import BeautifulSoup as bs
from pyquery import PyQuery as pq
import json
from sql_helper import MySqlHelper
USER_AGENT = 'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/70.0.3538.110 Safari/537.36'
headers = {
    'User-Agent':USER_AGENT
}


def read_jd_list(url, item_dict_all=None):
    if item_dict_all is None:
        item_dict_all = dict()
    response = requests.get(url, headers=headers)
    if response.status_code == 200:
        js = json.loads(response.text)
        result = js['data']['result']
        for i in result:
            item_dict = dict()
            a = i[i.find('|')+1:].split('|')
            print(a)
            item_dict['car'] = a[2] # 车次
            item_dict['depart'] = a[5] # 起始站
            item_dict['zongdian'] = a[6] # 终点站
            item_dict['go_time'] = a[7] # 出发时间
            item_dict['destination'] = a[8] # 到达时间
            item_dict['hao_time'] = a[9] # 耗时
            item_dict['datetime'] = a[12] # 日期
            item_dict['r_wo'] = a[22] # 软卧
            item_dict['wu_zuo'] = a[25] # 无座
            item_dict['ying_wo'] = a[27] # 硬卧
            item_dict['ying_zuo'] = a[28] # 硬座
            item_dict['v2'] = a[29] # 二等座
            item_dict['v1'] = a[30] # 一等座
            item_dict['tedeng'] = a[31] # 特等座
            item_dict_all[a[2]] = item_dict


    else:
        print("你抓的出问题了！")
    return item_dict_all


if __name__ == "__main__":

    url = "https://kyfw.12306.cn/otn/leftTicket/queryO?leftTicketDTO.train_date=2018-12-23&leftTicketDTO.from_station=BJP&leftTicketDTO.to_station=SHH&purpose_codes=ADULT"
    item_dict_all = read_jd_list(url)
    skuid_list = [sku for sku in item_dict_all.keys()]
    mysql = MySqlHelper()
    sql = "INSERT INTO 12306s(car, depart, zongdian, destination, go_time, dao_time, hao_time, datetime, r_wo, wu_zuo, ying_wo, v2, v1, tedeng) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
    values_list = [list(value.values()) for value in item_dict_all.values()]
    mysql.exec_many(sql, values_list)
